AppleWorks Spreadsheets
By Andrew Roughan
Copyright (c) 1994 Apple Users' Group, Sydney
Republished from Applecations, a publication of the Apple Users' Group, Sydney, Australia.
Here is a practical example of how to program and use an AppleWorks spreadsheet.
I recently received my competition book for the Autumn Squash Pennant and immediately started formulating the draw of my future matches by hand. It is a long process which involves repetitive yet careful application of a mysterious process which determines which team turns up where and when for the complete season. Mistakes can be embarrassing.
While I was flicking between the "who plays who in which round" page and the "we play on this night at this time" page and the page on which I wrote my entries, I started wondering if there was an easier or quicker way.
The manual process that I have used for so long is basically two table lookups. The first lookup finds an opponent for the round and determines if it is an away or home game, and then a second lookup in the grade table determines which team actually is the opponent, which night of the week do they play and at what time. These lookups then are repeated for the number of rounds in the competition.
This process lends itself fairly simply to an AppleWorks spreadsheet. The method should be quite simple to convert for different sports, different numbers of teams in a grade, or even to a different computer spreadsheet.
There are two basic sets of information that are needed. The first is the list of teams.
The second set of information is the draw for each round.
Round 1 Round 2
1 v 8 7 v 1
2 v 7 6 v 2
3 v 6 5 v 3
4 v 5 8 v 4
In my competition there are 14 rounds so I wont regurgitate them all here. They are all readily found in the competition book. Fortunately the squash draw is fixed so each pennant
uses the same draw. This fact is what makes this spreadsheet reuseable and why I will save time by using it in the future.
If my team is UTS (team number 6), then using the round one information I can determine that I have an away game (team number 6 appears in the second column) and that I am playing against team number 3. By referring back to the team list I can determine that team number 3 is Coogee SthA and that they play on Wednesday night at 8pm.
Using the same process for round two, I can determine that I'm playing Bondi Waverly at home on Friday night at 7.30pm.
So how can I get AppleWorks to tell me all that?
The first step is to give AppleWorks the list of teams.
Enter the team names in the first column, the night they play in the second and the time they play in the third (my data for eight teams extends from cell D3 across and down to cell F10. If yours is different, make sure you change the formulae which appear below accordingly). All three columns can be character layouts; no calculations are performed on the times or the day of the week.
If you want to extend your spreadsheet later you could possibly look at having a starting date for each round and then adding the day of week to the starting date to determine the absolute date of the game. This would require that the day of week be entered as a numeric value, but personally I didn't get this far.
Having entered the teams in the grade, you now have to tell AppleWorks which team you wish to create the draw for.
In a new row under the team names enter the number of your team. My team, UTS, is number 6. I have this information in cell F13. This cell number must be changed in the formulae which appear below if your spreadsheet is different.
NB: Note that if you insert rows before the first team name, AppleWorks will not associate number 6 with UTS. If you wish to insert rows, have another cell subtract the number of inserted rows from the row number that your team is in. This is necessary because the round information needs to work with the number 6 (rather than say 14) in order to calculate who team 6 is playing in each round.
The next step is to enter the round tables. In my competition with 14 rounds there is quite a lot of data and it is important that it is entered correctly. If you only want to use the spreadsheet with your team then you can ignore rows which do not have your team number. For future
reuseability with a different team number, you should consider entering the whole table.
Each round should take two columns. I started my columns off to the right of the screen because I did not want this data to display when the spreadsheet was reloaded. Place all the home teams in the first column and all the corresponding away teams in the second column.
Round 1 Round 2
1 8 7 1
2 7 6 2
3 6 5 3
4 5 8 4
In order to get a corresponding value from a second column given a value which appears in the first column, we need to use the built in AppleWorks function @LOOKUP. The nature of @LOOKUP is that it scans for the value in the first column and then returns the value from the second column in the same row. Unfortunately, it can't look backwards. In order for our spreadsheet to work we must duplicate data. Our table now becomes twice as deep:
Note that for each round each team appears in both the home and away columns. For future reference you should understand that if a team is considered the home team then they must appear in the first 4 rows of the round. Likewise, an away team appears in the last 4 rows of the round.
A second feature of the @LOOKUP command is that it scans through the column looking for the FIRST value which is less than or equal to the value asked for. As an example: a lookup for 7 in round 1 would find the value 4 in the first column and return the corresponding value 5. Also: a lookup for 1 in round 2 would return nothing.
This means that in order for @LOOKUP to work correctly, the first column of each round must be in ascending order. Now, because we also need the first 4 rows to only consist of home teams for each round, we need to further specify that the home list must be ascending and the away list must be ascending. Don't forget to move the corresponding values in the second column!
My spreadsheet has data for fourteen rounds and eight teams starting in cell H3 and extending across and down to cell AI11.
If you are sure that all your values are correct then you could shrink the table and make it hard to read by changing the layout width for the round columns to be 1 character wide.
Now its time to enter some formulae!
1) In cell B17, place this formula:
@IF(@OR(F13=H3,F13=H4,F13=H5,F13=H6),"H","A")
This formula searches for the specified team number (F13) in the home column of the round (H3-6). If it is found there, then "H" is returned. Otherwise "A" is returned.
This formula sets up the home or away status which is used to determine which lookup table is to be used by subsequent formulas. This is necessary because the formula length becomes too long for AppleWorks to handle if this is not done.
2) This next formula returns the number of the opposing team for the round using the home or away status (B17). Enter it in cell C17.
3) The following formula returns the name (D3-10) of the opposing team using the number which was returned by the last formula (C17). Enter it in cell D17.
@CHOOSE(C17,D3,D4,D5,D6,D7,D8,D9,D10)
4) Now we need to also return the day of week (E3-10) that the opposing team (C17) plays on or a blank if it is a home night. Enter this formula in cell E17.
That's all the formulae you'll need for the first round. The other rounds are copies which refer to the relevant round data. They should appear directly under the same formulae for round one. To do this, copy within the worksheet, highlight all five formulae, press return. Move the cursor down one line to the new location, press '.', move the cursor down 13 lines to highlight the destination for the thirteen remaining rounds, press return.
AppleWorks now asks whether you would like to copy the cell references within the formula with 'no change' or 'relative' to the cursor movements made to highlight the destination cell.
When asked about cells B17, C17 answer 'relative', otherwise answer 'no change'. This has the effect of making some changes for you.
To make the other changes necessary, place the cursor on cell B18 and press <Open Apple>-U. This brings up a copy of the formula for you to edit. This round two formula is referencing the round information for round one. Change the round one references from H3, H4, H5 and H6 to the round two references J3, J4, J5 and J6. You must do this manually for each round (B18 to B30) but make sure you get the right round reference's (by adding two columns for each round) otherwise the spreadsheet will fail (halfway hint: the round column after Z3 is AB3). B30 should reference cells AH3 to AH6. The same kind of edit must also be made on the formulae in cells C18 to C30.
It is possible to get Appleworks to do some of this editing for you but some manual editing would still be required. I have presented what I feel is the simplest and most understandable solution although it takes longer to type in.
After you've made the changes, press <Open Apple>-K to calculate the spreadsheet. You should get the same values as presented in figure 1.
Label: Formulae:
1) 2) 3) 4) 5)
...
home/
row: away: Opponent: Day: Time:
17:Round 1 A 3 Coogee SthA Wed 8:00
18:Round 2 H 2 Bondi Wav
19:Round 3 A 1 Clovelly Thu 7:30
20:Round 4 H 7 Leichardt
21:Round 5 A 8 Scopus Wed 8:00
...
That's it! Now you'll never have to flick between those dreaded pages again!
For those who want to avoid typing it all in, a copy of the spreadsheet is available for Download in the [E]ditor's section of the [F]iling cabinet on the Apple // BBS (02)449-7798
Permission is hereby granted for non-profit user groups to republish this content. PLEASE CREDIT THE AUTHOR AND THE SOURCE: Applecations, publication of the Apple Users' Group, Sydney, Australia